namespace AonesoftBase.DAL
{
    using AonesoftBase.Biz;
    using AonesoftBase.DBUtility;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class Common
    {
        private readonly string conn = string.Empty;

        public Common(string DBcommConn)
        {
            this.conn = DBcommConn;
        }

        public string AutoId(string Letter, string FieldName, string TableName)
        {
            string sQLString = "select max(substring(" + FieldName + ",2,4))+1 AS Id from " + TableName;
            object single = DbHelperSQL.GetSingle(this.conn, sQLString);
            int num = int.Parse(single.ToString());
            if (single == null)
            {
                return (Letter + "0001");
            }
            if ((num > 1) && (num < 9))
            {
                return (Letter + "000" + single.ToString());
            }
            if ((num > 10) && (num < 0x63))
            {
                return (Letter + "00" + single.ToString());
            }
            if ((num > 100) && (num < 0x3e7))
            {
                return (Letter + "0" + single.ToString());
            }
            if ((num > 0x3e8) && (num < 0x270f))
            {
                return (Letter + single.ToString());
            }
            return single.ToString();
        }

        public int CheckMark(string CheckFieldName, string PK, string TableName, string IdList)
        {
            if ((CheckFieldName == string.Empty) || CheckFieldName.Equals(""))
            {
                CheckFieldName = "CheckMark";
            }
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + TableName + " set " + CheckFieldName + "=1");
            if (!IdList.Trim().Equals(string.Empty) && (IdList.Trim() != ""))
            {
                IdList = IdList.Trim().Substring(0, IdList.LastIndexOf(","));
                builder.Append(" where " + PK + " in (" + IdList + ")");
            }
            return DbHelperSQL.ExecuteSql(this.conn, builder.ToString());
        }

        public bool CheckStatus(string CheckFieldName, string TableName, string strWhere)
        {
            if ((CheckFieldName == string.Empty) || CheckFieldName.Equals(""))
            {
                CheckFieldName = "CheckMark";
            }
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(*) from " + TableName);
            builder.Append(" where " + CheckFieldName + " =1 and " + strWhere);
            return this.Exists(builder.ToString());
        }

        public int Delete(string TableName, string strWhere)
        {
            string sQLString = "Delete " + TableName;
            if (strWhere != string.Empty)
            {
                sQLString = sQLString + " where " + strWhere;
            }
            return DbHelperSQL.ExecuteSql(this.conn, sQLString);
        }

        public bool ExecuteSqlTran(ArrayList SQLStringList)
        {
            return DbHelperSQL.ExecuteSqlTran(this.conn, SQLStringList);
        }

        public bool ExecuteSqlTran(Hashtable SQLStringList)
        {
            return DbHelperSQL.ExecuteSqlTran(this.conn, SQLStringList);
        }

        public bool ExecuteSqlTran(string SQLStringList)
        {
            return DbHelperSQL.ExecuteSqlTran(this.conn, SQLStringList);
        }

        public bool Exists(string strSql)
        {
            int num;
            object single = DbHelperSQL.GetSingle(this.conn, strSql);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public bool Exists(string TableName, string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + TableName + " where " + strWhere);
            return this.Exists(builder.ToString());
        }

        public bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int num;
            object objA = DbHelperSQL.GetSingle(this.conn, strSql, cmdParms);
            if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(objA.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public DataTable FreeQuery(string[] FieldName, string TableName, string strWhere)
        {
            DataSet set = new DataSet();
            string sQLString = "select ";
            for (int i = 0; i <= (FieldName.Length - 1); i++)
            {
                if (!FieldName[i].Equals(FieldName[FieldName.Length - 1]))
                {
                    sQLString = sQLString + FieldName[i] + ",";
                }
                else
                {
                    sQLString = sQLString + FieldName[i] + " ";
                }
            }
            string str2 = sQLString;
            sQLString = str2 + "from " + TableName + " " + strWhere;
            return DbHelperSQL.Query(this.conn, sQLString).Tables[0];
        }

        public string[] GetAjaxCompletionList(string FieldName, string TableName, string prefixText, int count)
        {
            return DbHelperSQL.GetAjaxCompletionList(this.conn, FieldName, TableName, prefixText, count);
        }

        public DataTable GetFieldType(string TableName)
        {
            string strWhere = string.Format("TABLE_NAME ='{0}'", TableName);
            string fieldName = "COLUMN_NAME,DATA_TYPE,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION";
            DataTable objObject = new DataTable();
            object cache = DataCache.GetCache(TableName);
            if (cache == null)
            {
                objObject = this.GetList(fieldName, "INFORMATION_SCHEMA.COLUMNS", strWhere, "").Tables[0];
                if (objObject != null)
                {
                    DataCache.SetCache(TableName, objObject, DateTime.Now.AddHours(2.0), TimeSpan.Zero);
                }
                return objObject;
            }
            return (DataTable) cache;
        }

        public DataTable GetFieldType(string TableName, string Fields)
        {
            string strWhere = string.Format("TABLE_NAME ='{0}'", TableName);
            string fieldName = "COLUMN_NAME,DATA_TYPE,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION";
            object cache = DataCache.GetCache(TableName);
            DataTable objObject = new DataTable();
            if (cache == null)
            {
                objObject = this.GetList(fieldName, "INFORMATION_SCHEMA.COLUMNS", strWhere, "").Tables[0];
                if (objObject != null)
                {
                    DataCache.SetCache(TableName, objObject, DateTime.Now.AddMinutes(2.0), TimeSpan.Zero);
                }
            }
            else
            {
                objObject = (DataTable) cache;
            }
            DataRow[] dr = objObject.Select("COLUMN_NAME='" + Fields + "'");
            return BaseCommon.DataRowConvertToDataSet(objObject, dr).Tables[0];
        }

        public DataSet GetList(string SQL)
        {
            if (SQL.Trim() != "")
            {
                return DbHelperSQL.Query(this.conn, SQL);
            }
            return null;
        }

        public DataTable GetList(string[] FieldName, string TableName, string strWhere)
        {
            return this.FreeQuery(FieldName, TableName, strWhere);
        }

        public DataSet GetList(string FieldName, string TableName, string strWhere, string orderList)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select " + FieldName);
            builder.Append(" FROM " + TableName);
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
                if (orderList.Trim() != "")
                {
                    builder.Append(" order by " + orderList);
                }
            }
            else if (orderList.Trim() != "")
            {
                builder.Append(" where 1=1 ");
                builder.Append(" order by " + orderList);
            }
            return DbHelperSQL.Query(this.conn, builder.ToString());
        }

        public int GetMax(string FieldName, string TableName)
        {
            string sQLString = "select max(" + FieldName + ")+1 from " + TableName;
            object single = DbHelperSQL.GetSingle(this.conn, sQLString);
            if (single == null)
            {
                return 1;
            }
            return int.Parse(single.ToString());
        }

        public string GetstrAarry(string FieldName, string TableName, string strWhere, string orderList)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select distinct " + FieldName);
            builder.Append(" FROM " + TableName);
            string str = null;
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
                if (orderList.Trim() != "")
                {
                    builder.Append(" order by " + orderList);
                }
            }
            else if (orderList.Trim() != "")
            {
                builder.Append(" where 1=1 ");
                builder.Append(" order by " + orderList);
            }
            DataSet set = DbHelperSQL.Query(this.conn, builder.ToString());
            for (int i = 0; i < set.Tables[0].Rows.Count; i++)
            {
                str = str + set.Tables[0].Rows[i][0].ToString() + ",";
            }
            return str.Substring(0, str.Length - 1);
        }

        public DataTable GetTableFieldInfo(string TableName)
        {
            string sQLString = string.Format("SELECT     \r\n                          表名= d.name  ,   \r\n                          表说明=   isnull(f.value,'')   ,   \r\n                          字段序号=a.colorder,   \r\n                          字段名=a.name,   \r\n                          标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   1 else  0   end,   \r\n                          主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (   \r\n                          SELECT   name   FROM   sysindexes   WHERE   indid   in(   \r\n                          SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   \r\n                          )))   then   1   else   0   end,   \r\n                          类型=b.name,   \r\n                          --长度=a.length,   \r\n                          长度=a.prec, \r\n                          精度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   \r\n                          小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   \r\n                          允许空=case   when   a.isnullable=1   then   1 else   0   end,   \r\n                          默认值=isnull(e.text,''),   \r\n                          字段说明=isnull(g.[value],'')   \r\n\r\n                          FROM   syscolumns   a   \r\n                          left   join   systypes   b   on   a.xtype=b.xusertype   \r\n                          inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and   d.status>=0   \r\n                          left   join   syscomments   e   on   a.cdefault=e.id   \r\n                          left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id       \r\n                          left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0   \r\n\r\n                          where   d.name='{0}'         --如果只查询指定表,加上此条件   \r\n                          order   by   a.id,a.colorder   ", TableName);
            return DbHelperSQL.Query(this.conn, sQLString).Tables[0];
        }

        public string[] GetTableFieldName(string TableName)
        {
            return DbHelperSQL.GetTableFieldName(this.conn, TableName);
        }

        public int InsertImg(string TableName, string ImgFieldName, byte[] fs, string strWhere)
        {
            string strSQL = string.Concat(new object[] { "Update ", TableName, " set ", ImgFieldName, "=", fs, "" });
            if (strWhere != string.Empty)
            {
                strSQL = strSQL + " where " + strWhere;
            }
            return DbHelperSQL.ExecuteSqlInsertImg(this.conn, strSQL, fs);
        }

        public int PageCount(string TableName, string strWhere)
        {
            string sQLString = "select count(1) from [" + TableName + "]";
            if ((strWhere != null) && (strWhere.Trim() != ""))
            {
                sQLString = sQLString + " where " + strWhere;
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(this.conn, sQLString));
        }

        public DataSet Pagination(string PK, string FieldName, string TableName, int pageSize, int pageIndex, string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("Select top ");
            builder.Append(pageSize.ToString());
            builder.Append(" ");
            builder.Append(FieldName);
            builder.Append(" from ");
            builder.Append(TableName);
            if (pageIndex > 0)
            {
                builder.Append(" where ");
                builder.Append(PK);
                builder.Append(" not in (select top ");
                builder.Append((pageSize * pageIndex).ToString());
                builder.Append(" " + PK);
                builder.Append(" from ");
                builder.Append(TableName);
                if (strWhere.Trim() != "")
                {
                    builder.Append(" where ");
                    builder.Append(strWhere);
                    builder.Append(" order by ");
                    builder.Append(PK);
                    builder.Append(") and ");
                    builder.Append(strWhere);
                }
                else
                {
                    builder.Append(" order by ");
                    builder.Append(PK + ")");
                }
            }
            else if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by ");
            builder.Append(PK);
            return DbHelperSQL.Query(this.conn, builder.ToString());
        }

        public int RunProc(string ProcName)
        {
            string sQLString = "Exec " + ProcName;
            return DbHelperSQL.ExecuteSql(this.conn, sQLString);
        }

        public int RunProc(string ProcName, string ParaList)
        {
            string sQLString = "Exec " + ProcName + " " + ParaList;
            return DbHelperSQL.ExecuteSql(this.conn, sQLString);
        }

        public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            return DbHelperSQL.RunProcedure(this.conn, storedProcName, parameters);
        }

        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            return DbHelperSQL.RunProcedure(this.conn, storedProcName, parameters, tableName);
        }

        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            return DbHelperSQL.RunProcedure(this.conn, storedProcName, parameters, out rowsAffected);
        }

        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            return DbHelperSQL.RunProcedure(this.conn, storedProcName, parameters, tableName, Times);
        }

        public DataSet TwoTableQuery(string strWhereA, string TableA, string TableB, string strWhereB, string ID)
        {
            return DbHelperSQL.TwoTableQuery(this.conn, strWhereA, TableA, TableB, strWhereA, ID);
        }

        public int UnCheckMark(string CheckFieldName, string PK, string TableName, string IdList)
        {
            if ((CheckFieldName == string.Empty) || CheckFieldName.Equals(""))
            {
                CheckFieldName = "CheckMark";
            }
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + TableName + " set " + CheckFieldName + "=0");
            if (!IdList.Trim().Equals(string.Empty) && (IdList.Trim() != ""))
            {
                IdList = IdList.Trim().Substring(0, IdList.LastIndexOf(","));
                builder.Append(" where " + PK + " " + IdList);
            }
            return DbHelperSQL.ExecuteSql(this.conn, builder.ToString());
        }

        public int Update(string FieldExpression, string TableName, string strWhere)
        {
            string sQLString = "Update " + TableName + " set " + FieldExpression;
            if (strWhere != string.Empty)
            {
                sQLString = sQLString + " where " + strWhere;
            }
            return DbHelperSQL.ExecuteSql(this.conn, sQLString);
        }
    }
}

